package com.tgb.lk.table;

import java.sql.*;
import javax.sql.rowset.CachedRowSet;

public class OracleTableManager extends TableManager {

	private OracleTableManager() {
	}

	public static TableManager getInstance() {
		return _instance;
	}

	@Override
	public Table getTableInfo(String name) {
		Table table = new Table();
		table.setName(StringUtil.ruleJavaName(name));
		TableConfig config = TableConfig.getInstance();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = DBManager.getInstance().getConnection(
					config.getDbConnectString(), config.getDbUsername(),
					config.getDbPasswd());
			st = conn.createStatement(1005, 1007);
			rs = st
					.executeQuery("select utcom.column_name,utcom.data_type,utcom.data_length length,data_precision,data_scale,utcom.NULLABLE,ucc.comments memo"
							+ " from sys.user_tab_columns utcom join USER_COL_COMMENTS ucc on (utcom.TABLE_NAME = ucc.table_name and utcom.COLUMN_NAME = ucc.column_name) "
							+ " Where utcom.table_name='"
							+ name.toUpperCase()
							+ "'");
			int num;
			for (num = 0; rs.next(); num++) {
				;
			}
			rs.beforeFirst();
			TableColumn[] columns = new TableColumn[num];
			table.setColumnNum(num);
			String tmp = "";
			String c[] = new String[num];
			int t[] = new int[num];
			boolean[] nullable = new boolean[num];
			int i = 0;
			while (rs.next()) {
				c[i] = rs.getString("column_name").toLowerCase().trim();
				tmp = rs.getString("data_type").toLowerCase().trim();
				if ("number".equals(tmp) && rs.getInt("data_scale") > 0) {
					tmp = tmp + "f";
				}
				nullable[i] = ("N".equals(rs.getString("NULLABLE"))) ? false
						: true;

				TableColumn column = new TableColumn();
				column.setId(i + 1);
				column.setColumnName(c[i]);
				column.setColumnType(tmp);
				column.setNullable(nullable[i]);
				column.setKey(false);
				column.setLength(rs.getLong("length"));
				column.setMemo(rs.getString("memo"));
				columns[i] = column;

				t[i++] = DataType.getInstance().getTypeMap(tmp);
			}
			table.setColumnName(c);
			table.setColumnType(t);
			table.setNullable(nullable);
			table.setColumns(columns);
			DBManager.getInstance().cleanup(null, null, rs);
			rs = st
					.executeQuery("select c.index_name,c.column_name,data_type,data_precision,data_scale  from Sys.user_constraints i,Sys.all_ind_columns c,sys.user_tab_columns u  Where i.TABLE_NAME ='"
							+ name.toUpperCase()
							+ "' And  i.CONSTRAINT_TYPE='P' And i.CONSTRAINT_name=c.index_name  "
							+ "And c.column_name=u.column_name And u.table_name='"
							+ name.toUpperCase() + "' Order By index_name ");
			int[] keyPos = getKeyPos(c, getKey(rs));
			table.setKeyPos(keyPos);
			for (int j : keyPos) {
				columns[j].setKey(true);
			}
			if (getKey(rs) != null && getKey(rs).length > 0) {
				table.setPkName(getKey(rs)[0]);
			}
		} catch (Exception e) {
			Log.log(e.getMessage());
		} finally {
			DBManager.getInstance().cleanup(conn, st, rs);
		}
		return table;
	}

	@Override
	public String[] getAllTableName() {
		String tables[] = null;
		String sql = "select * from sys.all_tables where owner='"
				+ TableConfig.getInstance().getDbUsername().toUpperCase()
				+ "' order by table_name";
		CachedRowSet crs = DBManager.getInstance().executeQuery(sql, null);
		try {
			tables = new String[crs.size()];
			int i = 0;
			while (crs.next()) {
				tables[i++] = crs.getString("table_name").toLowerCase();
			}
		} catch (Exception e) {
			Log.log(e);
		}
		return tables;
	}

	private static OracleTableManager _instance = new OracleTableManager();

}